import socket
import queue
import threading
import re
import telnetlib
import time,datetime
import MySQLdb

server = socket.socket(socket.AF_INET,socket.SOCK_DGRAM)
server.bind(('0.0.0.0',514))
lock = threading.RLock()
q = queue.Queue()
d = {}
conn = MySQLdb.connect(host='mysql-server',port=3306,user='ccd',passwd='123456',db='computer_status', charset="utf8")
cur = conn.cursor()
# 主线程接收防火墙传来的日志
def SyslogToPrint():
    global q
    while True:
        try:
            data = server.recvfrom(8092)
            (LogMsg,host) = data
            LogMsg = LogMsg.decode('utf-8')
            q.put(LogMsg)
            if not q.empty():
                t_sql = threading.Thread(target=WritSQL)
                t_sql.start()
        except Exception as e:
            print(e)

# SyslogToPrint()

# 获取数据库信息,暂未使用
def GetMysqldb():
    sql = "SELECT ip_number,ip_name,ip_seat_number,ip_asset_users,ip_mac FROM `computerstatus_ipassets`"
    cur.execute(sql)

# 线程更新交换机数据
def Jiaohuanji():
    global d
    t = threading.Timer(3600,Jiaohuanji)
    password = "PcFree2014"
    tip = "192.168.0.1"
    cmd = "show arp"
    confComplete = ""
    moreReg = '--More--'

    try:
        tn = telnetlib.Telnet(tip, port=23, timeout=50)
        tn.read_until(b"Password:")
        tn.write(password.encode('ascii') + b'\n')
        time.sleep(0.5)
    except Exception as e:
        print(e)
        tn.close()

    tn.write(b'\n')
    tn.write(b'enable\n')
    tn.read_until(b'Password:')
    tn.write(password.encode('ascii') + b'\n')
    tn.write(cmd.encode('ascii') + b'\n')
    time.sleep(0.5)
    msg = tn.read_very_eager().decode('utf-8')
    moerFlag = re.search(moreReg, msg)
    confComplete = re.sub(moreReg, ' ', confComplete)
    confComplete = confComplete + msg

    while (moerFlag):
        tn.write(' '.encode('ascii'))
        time.sleep(0.2)
        msg = tn.read_very_eager().decode('utf-8')
        moerFlag = re.search(moreReg, msg)
        confComplete = re.sub(moreReg, ' ', confComplete)
        confComplete += msg
    tn.write('exit'.encode('ascii'))
    tn.close()
    # print(str(confComplete).split())
    ccd = str(confComplete).split()

    global list1
    list1 = {}
    lock.acquire()
    # 数据清洗
    for x in range(0, len(ccd)):
        if ccd[x] == "Internet" and ccd[x+3] != "Incomplete":
            mac = list(ccd[x+3].replace(".",""))
            mac1 = ''
            for i in range(len(mac)):
                if i != 0 and i %2 ==0:
                    mac1 = mac1 + ":" + mac[i]
                else:
                    mac1 = mac1 + mac[i]
            dict1 = {ccd[x+1]:mac1}
            list1.update(dict1)
    lock.release()
    d = list1
    t.start()
    tn.close()

# 线程清洗数据,写入数据库
def WritSQL():
    global q
    global lock
    global cur

    while not q.empty():
        str_s = q.get()
        s = str_s.split(" ")
        list1 = []
        list1.append(s[0] + s[1] + s[2] + s[3])
        s = s[4:]
        s = str(s)[2:-2].split(";")

        for i in range(len(s)):
            # url字段的提取
            if i == len(s) - 1:
                list1.append(s[i][4:])
            elif i == 0:
                try:
                    x = s[i].split('=')
                    dateArray = datetime.datetime.utcfromtimestamp(int(x[1]))
                    otherStyleTime = dateArray.strftime("%Y-%m-%d %H:%M:%S")
                    list1.append(otherStyleTime)
                except Exception as e:
                    print(e)
                    list1.append(" ")
            else:
                x = s[i].split('=')
                if len(x) == 1:
                    list1.append(" ")
                else:
                    if x[1]:
                        list1.append(x[1])
                    else:
                        list1.append("无参数")
        # 写入联动数据
        lock.acquire()
        one_number = ()
        two_number = ""
        print(list1)
        try:
            if cur.execute("SELECT Keyword from `networkstatus_urlkeyword` where url = '{}'".format(list1[5])):
                two_number = cur.fetchone()[0]

            else:
                two_number = "未匹配到URL参数"
            if list1[3] in d:
                sql = "SELECT ip_number,ip_seat_number,ip_asset_users,ip_mac FROM `computerstatus_ipassets` where ip_mac='{}'".format(
                    d[list1[3]])
                flag = cur.execute(sql)
                if flag:
                    one_number = cur.fetchone()
                    sql = "insert into `networkstatus_networklog`(time ,deviceip,srcip,username,site,url,mac,complete,asset_users,seat_number,it_number,url_keyword) value ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(
                        list1[1], list1[2], list1[3], list1[4], list1[5], list1[6], d[list1[3]], str_s, one_number[2],
                        one_number[1], one_number[0], two_number)
                    cur.execute(sql)
                    conn.commit()
                else:
                    sql = "insert into `networkstatus_networklog`(time ,deviceip,srcip,username,site,url,mac,complete,asset_users,seat_number,it_number,url_keyword) value ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(
                        list1[1], list1[2], list1[3], list1[4], list1[5], list1[6], d[list1[3]], str_s, "未匹配到数据", "未匹配到数据",
                        "未匹配到数据", two_number)
                    cur.execute(sql)
                    conn.commit()
            else:
                print(str_s)
        except Exception as e:
            print("==="*25)
            print(e)
            print(len(one_number))
            print(len(two_number))
            print(len(list1))
            print(list1)
            print("==="*25)
        lock.release()

def main():
    Jiaohuanji()
    SyslogToPrint()

if __name__ == '__main__':
    main()